using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Threading;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;



namespace WebService.Nafania.DataLayer.Model.Base
{
	public class AsyncAdoRepository : AdoRepository
	{
		#region Constructor

		public AsyncAdoRepository()
		{
			ConnectionString = new SqlConnectionStringBuilder(GetConfigConnectionString())
			                   	{
			                   		AsynchronousProcessing = true
			                   	}.ToString();
		}

		#endregion

		#region Overrides of AdoRepository

		protected override sealed string ConnectionString { get; set; }

		protected override int ExecuteNonQuery(DbCommand dbCommand, DbTransaction dbTransaction)
		{
			IAsyncResult asyncResult = _transaction != null
			                           	? db.BeginExecuteNonQuery(dbCommand, _transaction, null, null)
			                           	: db.BeginExecuteNonQuery(dbCommand, null, null);
			WaitAsync(asyncResult);
			return db.EndExecuteNonQuery(asyncResult);
		}

		protected override IDataReader ExecuteReader(DbCommand dbCommand, DbTransaction dbTransaction)
		{
			IAsyncResult asyncResult = _transaction != null
			                           	? db.BeginExecuteReader(dbCommand, _transaction, null, null)
			                           	: db.BeginExecuteReader(dbCommand, null, null);
			WaitAsync(asyncResult);
			return db.EndExecuteReader(asyncResult);
		}

		protected override object ExecuteScalar(DbCommand dbCommand, DbTransaction dbTransaction)
		{
			IAsyncResult asyncResult = dbTransaction != null
			                           	? db.BeginExecuteScalar(dbCommand, dbTransaction, null, null)
			                           	: db.BeginExecuteScalar(dbCommand, null, null);
			WaitAsync(asyncResult);
			return db.EndExecuteScalar(asyncResult);
		}

		#endregion

		#region Private

		private int _timeCounter;

		private const int _AsynWait = 100;

		private void WaitAsync(IAsyncResult result)
		{
			_timeCounter = 0;

			while (!result.IsCompleted)
			{
				_timeCounter++;
				//Console.WriteLine("Waiting ({0})", _timeCounter);
				// Wait for 1/10 second, so the counter
				// does not consume all available resources 
				// on the main thread.
				Thread.Sleep(_AsynWait);
			}
		}

		#endregion
	}

	public class SyncAdoRepository : AdoRepository
	{
		#region Constructor

		public SyncAdoRepository()
		{
			ConnectionString = new SqlConnectionStringBuilder(GetConfigConnectionString())
			                   	{
			                   		AsynchronousProcessing = false
			                   	}.ToString();
		}

		#endregion

		#region Overrides of AdoRepository

		protected override sealed string ConnectionString { get; set; }

		protected override int ExecuteNonQuery(DbCommand dbCommand, DbTransaction dbTransaction)
		{
			return dbTransaction != null 
				?	db.ExecuteNonQuery(dbCommand, dbTransaction)
				: db.ExecuteNonQuery(dbCommand);
		}

		protected override IDataReader ExecuteReader(DbCommand dbCommand, DbTransaction dbTransaction)
		{
			return dbTransaction != null
				? db.ExecuteReader(dbCommand, dbTransaction)
				: db.ExecuteReader(dbCommand);
		}

		protected override object ExecuteScalar(DbCommand dbCommand, DbTransaction dbTransaction)
		{
			return dbTransaction != null
				? db.ExecuteScalar(dbCommand, dbTransaction)
				: db.ExecuteScalar(dbCommand);
		}

		#endregion
	}

	public abstract class AdoRepository : IDisposable
	{
		#region Initialization


		//private static readonly string _configConnectionString;

		protected abstract string ConnectionString { get; set; }


		protected static string GetConfigConnectionString()
		{
		  const string _MediaPlayerConnectionName = "nafania";
			var conString = ConfigurationManager.ConnectionStrings[_MediaPlayerConnectionName];

			if (conString == null || String.IsNullOrWhiteSpace(conString.ConnectionString))
				throw new ApplicationException(String.Format("Couldn't find Connection string: '{0}'", _MediaPlayerConnectionName));

			return conString.ConnectionString;

		}

		protected DbTransaction _transaction = null;
		protected SqlDatabase db;
		private DbConnection _connection;

		private readonly bool _useTransaction = false;
		private readonly IsolationLevel _isolation = IsolationLevel.Serializable;

		protected AdoRepository(){}

		protected AdoRepository(bool useTransaction = false, IsolationLevel isolation = IsolationLevel.Serializable)
		{
			_useTransaction = useTransaction;
			_isolation = isolation;
		}

		public T ExecuteProcedureReader<T>(string spName, List<SpParam> spParams, Func<IDataReader, T> parse) where T : class 
		{
			db = new SqlDatabase(ConnectionString);

			DbCommand dbCommand = db.GetStoredProcCommand(spName);

			if (spParams != null)
				foreach (var param in spParams)
					db.AddInParameter(dbCommand, param.Name, param.Type, param.Value);

			using (_connection = db.CreateConnection())
			{
				_connection.Open();

				if (_useTransaction)
				{
					_transaction = _connection.BeginTransaction(_isolation);
				}

				try
				{
					T result;

					using (IDataReader reader = ExecuteReader(dbCommand, _transaction))
					{
						result = parse(reader);
					}

					if (_transaction != null)
						_transaction.Commit();

					return result;
				}
				catch
				{
					if (_transaction != null)
						_transaction.Rollback();

					throw;
				}
				finally
				{
					FinalizeConnection();
				}
			}
		}

		public int ExecuteProcedureNonQuery<T>(string spName, List<SpParam> spParams) where T : class
		{
			db = new SqlDatabase(ConnectionString);

			DbCommand dbCommand = db.GetStoredProcCommand(spName);

			if (spParams != null)
				foreach (var param in spParams)
					db.AddInParameter(dbCommand, param.Name, param.Type, param.Value);

			using (_connection = db.CreateConnection())
			{
				_connection.Open();

				if (_useTransaction)
				{
					_transaction = _connection.BeginTransaction(_isolation);
				}

				try
				{
					var result = ExecuteNonQuery(dbCommand, _transaction);

					if (_transaction != null)
							_transaction.Commit();

					return result;
				}
				catch
				{
					if (_transaction != null)
						_transaction.Rollback();
					throw;
				}
				finally
				{
					FinalizeConnection();
				}
			}
		}

		public T ExecuteProcedureScalar<T>(string spName, List<SpParam> spParams) where T : class 
		{
			db = new SqlDatabase(ConnectionString);

			DbCommand dbCommand = db.GetStoredProcCommand(spName);

			if (spParams != null)
				foreach (var param in spParams)
					db.AddInParameter(dbCommand, param.Name, param.Type, param.Value);

			using (_connection = db.CreateConnection())
			{
				_connection.Open();

				if (_useTransaction)
				{
					_transaction = _connection.BeginTransaction(_isolation);
				}

				try
				{
					T result = (T)ExecuteScalar(dbCommand, _transaction);

					if (_transaction != null)
						_transaction.Commit();

					return result;
				}
				catch
				{
					if (_transaction != null)
						_transaction.Rollback();

					throw;
				}
				finally
				{
					FinalizeConnection();
				}
			}
		}

		protected internal Exception SqlInfoEx = null;
		public void OnSqlInfoMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
		{
			//checks for any errors.
			if (e.Errors.Count > 0)
			{
				StringBuilder sbError = new StringBuilder();
				foreach (SqlError error in e.Errors)
				{
					sbError.AppendLine(error.Number + " = " + error.Message);
				}

				SqlInfoEx = new StoredProcedureException(sbError.ToString());
			}
		}

		#endregion

		#region Async Methods

		protected abstract int ExecuteNonQuery(DbCommand dbCommand, DbTransaction dbTransaction);

		protected abstract IDataReader ExecuteReader(DbCommand dbCommand, DbTransaction dbTransaction);

		protected abstract object ExecuteScalar(DbCommand dbCommand, DbTransaction dbTransaction);

		#endregion


		#region Implementation of IDisposable

		public void Dispose()
		{
			FinalizeConnection();


/*
			SqlConnection.InfoMessage -= OnSqlInfoMessageEventHandler;

			if (SqlCommand != null)
			{
				SqlCommand.Dispose();
				SqlCommand = null;
			}

			if (SqlConnection != null)
			{
				SqlConnection.Dispose();
				SqlConnection = null;
			}
*/
		}

		private void FinalizeConnection()
		{
			if (_connection != null)
			{
				_connection.Close();
				_connection.Dispose();
				_connection = null;
			}
		}

		#endregion

		#region Properties

		protected DateTime TimeStamp { get; private set; }

/*
		public SqlConnection SqlConnection;
		public SqlCommand SqlCommand; 
*/

		#endregion
	}

	public struct SpParam
	{
		public string Name;
		public DbType Type;
		public Object Value;
	}
}